package com.xch.dao.impl;

import com.xch.bean.Courier;
import com.xch.bean.Express;
import com.xch.dao.BaseCourierDao;
import com.xch.exception.DuplicateCodeException;
import com.xch.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CourierDaoMysql implements BaseCourierDao {
    private static final String SQL_FIND_ALL = "SELECT * FROM COURIER";
    private static final String SQL_FIND_LIMIT = "SELECT * FROM COURIER LIMIT ?,?";
    private static final String SQL_FIND_BY_PHONE = "SELECT * FROM COURIER WHERE COURIERPHONE=?";
    private static final String SQL_INSERT = "INSERT INTO COURIER (COURIERNAME,COURIERPHONE,COURIERID,PASSWORD,COURIEREXPRESSNUM,COURIERREGISTERTIME,COURIERLOGINTIME) VALUES(?,?,?,?,20,Now(),NOW())";
    private static final String SQL_UPDATE = "UPDATE COURIER SET COURIERNAME=?,COURIERPHONE=?,COURIERID=?,PASSWORD=? WHERE ID=?";
    private static final String SQL_DELETE = "DELETE FROM COURIER WHERE ID=?";
    private static final String SQL_TOTAL = "SELECT COUNT(ID) size FROM COURIER";

    @Override
    public int total() {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        int num=0;
        try {
            state = conn.prepareStatement(SQL_TOTAL);
            result = state.executeQuery();
            System.out.println(result.next());
            num = result.getInt(1);
            return num;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,result);
        }
        return num;
    }

    /**
     * 用于查询所有快递员
     *
     * @param limit      是否分页标记，true 分页，false 不分页 查询所有快递员
     * @param offset     sql语句的起始索引
     * @param pageNumber 每页查询数量
     * @return 快递集合
     */
    @Override
    public List<Courier> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<Courier> data = new ArrayList<>();
        //1.获取数据连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译sql语句
        try {
            if(limit){
                state = conn.prepareStatement(SQL_FIND_LIMIT);
                //3.填充参数（可选）
                state.setInt(1,offset);
                state.setInt(2,pageNumber);
            }else{
                state = conn.prepareStatement(SQL_FIND_ALL);
            }
            //4.执行sql语句
            result = state.executeQuery();
            while(result.next()){
                int id = result.getInt("id");
                String courierName = result.getString("courierName");
                String courierPhone = result.getString("courierPhone");
                String courierId = result.getString("courierId");
                String password = result.getString("password");
                int courierExpressNum = result.getInt("courierExpressNum");
                Timestamp courierRegisterTime = result.getTimestamp("courierRegisterTime");
                Timestamp courierLoginTime = result.getTimestamp("courierLoginTime");
                Courier c = new Courier(id,courierName,courierPhone,courierId,password,courierExpressNum,courierRegisterTime,courierLoginTime);
                data.add(c);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return data;
    }

    /**
     * 根据手机号查询快递员
     *
     * @param courierPhone 手机号
     * @return 快递信息，不存在时，返回null
     */
    @Override
    public Courier findByPhone(String courierPhone) {
        //1.获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译sql语句
        try {
            state = conn.prepareStatement(SQL_FIND_BY_PHONE);
            //3.填充参数
            state.setString(1,courierPhone);
            //4.执行sql语句
            result = state.executeQuery();
            //5.获取执行的结果
            if (result.next()){
                int id = result.getInt("id");
                String courierName = result.getString("courierName");
                String courierId = result.getString("courierId");
                String password = result.getString("password");
                int courierExpressNum = result.getInt("courierExpressNum");
                Timestamp courierRegisterTime = result.getTimestamp("courierRegisterTime");
                Timestamp courierLoginTime = result.getTimestamp("courierRegisterTime");
                Courier c = new Courier(id,courierName,courierPhone,courierId,password,courierExpressNum,courierRegisterTime,courierLoginTime);
                return c;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,result);
        }

        return null;
    }

    /**
     * 快递员的录入
     *INSERT INTO COURIER (COURIERNAME,COURIERPHONE,COURIERID,pASSWORD,COURIEREXPRESSNUM,COURIERREGISTERTIME,COURIERLOGINTIME) VALUES(?,?,?,?,20,Now(),NOW())
     * @param c 要录入的对象
     * @return 录入的结果
     */
    @Override
    public boolean insert(Courier c) throws DuplicateCodeException {
        //1.获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.预编译sql语句
        try {
            state = conn.prepareStatement(SQL_INSERT);
            //3.填充参数
            state.setString(1,c.getCourierName());
            state.setString(2,c.getCourierPhone());
            state.setString(3,c.getCourierId());
            state.setString(4,c.getPassword());
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 快递的修改
     *UPDATE COURIER SET COURIERNAME=?,COURIERPHONE=?,COURIERID=?,pASSWORD=? WHERE ID=?
     * @param id         要修改的快递员id
     * @param newCourier 要修改的快递员对象(courierName,courierPhone,courierId,password)
     * @return 录入的结果
     */
    @Override
    public boolean update(int id, Courier newCourier) {
        //1.获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.预编译语句
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            //3.设置参数
            state.setString(1,newCourier.getCourierName());
            state.setString(2,newCourier.getCourierPhone());
            state.setString(3,newCourier.getCourierId());
            state.setString(4,newCourier.getPassword());
            state.setInt(5,id);
            //4,5 执行sql语句并获取结果
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 根据id，删除单个快递员信息
     *
     * @param id 要删除的快递员id
     * @return 删除的结果
     */
    @Override
    public boolean delete(int id) {
        //1.获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.预编译sql语句
        try {
            state=conn.prepareStatement(SQL_DELETE);
            state.setInt(1,id);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }
}
